package com.api.gbhg.huangwm.cron;

import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.general.GCONST;
import weaver.interfaces.schedule.BaseCronJob;

/**
 * @Author Huangwm
 * @Date 2022-09-30
 * @Version 1.0
 * @Description TODO 收入预算已用金额反向更新
 **/
public class SyncQuDaoZhiChu extends BaseCronJob {
    public static void main(String[] args) throws Exception {
        String rootPath = "D:\\WEAVER\\ecology\\";
        GCONST.setRootPath(rootPath);
        GCONST.setServerName("ecology");

        RecordSet rs = new RecordSet();
        rs.execute("SELECT DISTINCT dw FROM dbo.uf_zyczbksr_xj");
        while (rs.next()){
            int dwid = rs.getInt("dw");
            //updateCzbk(dwid);
            updateFCzbk(dwid);
        }

    }
    @Override
    public void execute() {

    }

    //更新财政拨款收入预算渠道核批数据
    public static void updateCzbk(int dwid){
        //update uf_zyczbksr_xj set yhpje=0,yhpsy=0,yjsrsy=yjsr,yqndyjjzyyje=0,yqndyjjzsyje=yqndyjjzje,snjzhpje=0,snjzhpsyje=0
        //update uf_zyczbksr_km set yhpje=0,hpsyje=0,yjsrsy=yjsr,yqndyjjzyyje=0,yqndyjjzsyje=yqndyjjzje,snjzhpje=0,snjzhpsyje=0
        //先查询下级所关联的科目级id
        String sql1 = "select distinct szgnkm from uf_zyczbksr_xj where srnf=2023 and dw="+dwid ;
        //再查询没有关联下级的科目级id（这部分只需要操作科目级表）
        String sql2 = "select id from uf_zyczbksr_km where dw="+dwid+" and id not in(select distinct szgnkm from uf_zyczbksr_xj where srnf=2023 and dw="+dwid+") ";

        RecordSet rs1 = new RecordSet();
        RecordSet rs2 = new RecordSet();
        RecordSet rs3 = new RecordSet();
        RecordSetTrans rs = new RecordSetTrans();
        rs.setAutoCommit(false);
        rs1.execute(sql1);
        boolean r = true;
        while (rs1.next()){
            String kmid = rs1.getString("szgnkm");//科目级id
            //根据科目级id循环科目下级表，找到对应的科目下级id进行查找核批数据
            String sql3 = "select * from uf_zyczbksr_xj where szgnkm ="+kmid;
            rs2.execute(sql3);
            while (rs2.next()){
                String xjid = rs2.getString("id");//科目下级id
                String sjzxsr = rs2.getString("sjzxsr");//上级专项收入 用于更新上级
                String getDNHpje = "select isnull(sum(hpje),0)as hpje from uf_ysbzyxeshpsj_dt2 where zyczbksr='28_"+xjid+"'";
                rs3.execute(getDNHpje);
                rs3.next();
                Double dnhpje = rs3.getDouble("hpje");//渠道的核批金额

                String getSNHpje = "select isnull(sum(hpje),0)as hpje from uf_ysbzyxeshpsj_dt1 where zyczbksr='28_"+xjid+"'";
                rs3.execute(getSNHpje);
                rs3.next();
                Double snhpje = rs3.getDouble("hpje");//渠道的核批金额

                try {
                    updateDNHP_XJ(rs,xjid,dnhpje,sjzxsr,kmid);
                    updateSNHP_XJ(rs,xjid,snhpje,sjzxsr,kmid);
                }catch (Exception e){
                    r = false;
                    e.printStackTrace();
                }
            }
        }

        RecordSet rs4 = new RecordSet();
        rs4.execute(sql2);
        while (rs4.next()){
            String kmid = rs4.getString("id");//科目级id

            String getDNHpje = "select isnull(sum(hpje),0)as hpje from uf_ysbzyxeshpsj_dt2 where zyczbksr='27_"+kmid+"'";
            rs3.execute(getDNHpje);
            rs3.next();
            Double dnhpje = rs3.getDouble("hpje");//渠道的核批金额

            String getSNHpje = "select isnull(sum(hpje),0)as hpje from uf_ysbzyxeshpsj_dt1 where zyczbksr='27_"+kmid+"'";
            rs3.execute(getSNHpje);
            rs3.next();
            Double snhpje = rs3.getDouble("hpje");//渠道的核批金额

            try {
                updateDNHP_KM(rs,kmid,dnhpje);
                updateSNHP_KM(rs,kmid,snhpje);
            }catch (Exception e){
                r = false;
                e.printStackTrace();
            }

        }
        if(!r){
            rs.rollback();
        }else {
            rs.commit();
        }
    }

    /**
     * 从下级更新当年核批
     * @param id
     * @param hpje
     * @param sjzxsr
     */
    public static void updateDNHP_XJ(RecordSetTrans rs,String id,Double hpje,String sjzxsr,String kmid) throws Exception {
        boolean result1 = rs.executeUpdate("update uf_zyczbksr_xj set yhpje=yhpje+"+hpje+",yhpsy=yhpsy+"+hpje+",yjsrsy=yjsr-yhpje-"+hpje+" where id="+id);
        System.err.println("更新科目下级表当年核批 id="+id+" 更新核批金额为"+hpje+" 更新结果为"+result1);
        if(!sjzxsr.isEmpty()){
            boolean result2 = rs.executeUpdate("update uf_zyczbksr_xj set yhpje=yhpje+"+hpje+",yhpsy=yhpsy+"+hpje+",yjsrsy=yjsr-yhpje-"+hpje+" where id="+sjzxsr);
            System.err.println("更新科目下级表上级收入当年核批 id="+sjzxsr+" 更新核批金额为"+hpje+" 更新结果为"+result2);
        }
        boolean result3 = rs.executeUpdate("update uf_zyczbksr_km set yhpje=yhpje+"+hpje+",hpsyje=hpsyje+"+hpje+",yjsrsy=yjsr-yhpje-"+hpje+" where id="+kmid);
        System.err.println("更新科目表当年核批 id="+kmid+" 更新核批金额为"+hpje+" 更新结果为"+result3);
    }

    /**
     * 从下级更新上年核批
     * @param id
     * @param snhpje
     * @param sjzxsr
     * @param kmid
     */
    public static void updateSNHP_XJ(RecordSetTrans rs,String id,Double snhpje,String sjzxsr,String kmid) throws Exception {
        boolean result1 = rs.executeUpdate("update uf_zyczbksr_xj set snjzhpje=snjzhpje+"+snhpje+",snjzhpsyje=snjzhpsyje+"+snhpje+",yqndyjjzsyje=yqndyjjzje-snjzhpje-"+snhpje+" where id="+id);
        System.err.println("更新科目下级表上年核批 id="+id+" 更新核批金额为"+snhpje+" 更新结果为"+result1);
        if(!sjzxsr.isEmpty()){
            boolean result2 = rs.executeUpdate("update uf_zyczbksr_xj set snjzhpje=snjzhpje+"+snhpje+",snjzhpsyje=snjzhpsyje+"+snhpje+",yqndyjjzsyje=yqndyjjzje-snjzhpje-"+snhpje+" where id="+sjzxsr);
            System.err.println("更新科目下级表上级收入上年核批 id="+sjzxsr+" 更新核批金额为"+snhpje+" 更新结果为"+result2);
        }
        boolean result3 = rs.executeUpdate("update uf_zyczbksr_km set snjzhpje=snjzhpje+"+snhpje+",snjzhpsyje=snjzhpsyje+"+snhpje+",yqndyjjzsyje=yqndyjjzje-snjzhpje-"+snhpje+" where id="+kmid);
        System.err.println("更新科目表上年核批 id="+kmid+" 更新核批金额为"+snhpje+" 更新结果为"+result3);
    }

    /**
     * 从科目级更新当年核批
     * @param id
     * @param hpje
     */
    public static void updateDNHP_KM(RecordSetTrans rs,String id,Double hpje) throws Exception {
        boolean result3 = rs.executeUpdate("update uf_zyczbksr_km set yhpje=yhpje+"+hpje+",hpsyje=hpsyje+"+hpje+",yjsrsy=yjsr-yhpje-"+hpje+" where id="+id);
        System.err.println("更新科目表当年核批 id="+id+" 更新核批金额为"+hpje+" 更新结果为"+result3);
    }

    /**
     * 从科目级更新上年核批
     * @param id
     * @param snhpje
     */
    public static void updateSNHP_KM(RecordSetTrans rs,String id,Double snhpje) throws Exception {
        boolean result3 = rs.executeUpdate("update uf_zyczbksr_km set snjzhpje=snjzhpje+"+snhpje+",snjzhpsyje=snjzhpsyje+"+snhpje+",yqndyjjzsyje=yqndyjjzje-snjzhpje-"+snhpje+" where id="+id);
        System.err.println("更新科目表上年核批 id="+id+" 更新核批金额为"+snhpje+" 更新结果为"+result3);
    }

    //更新非财政拨款收入预算渠道核批数据
    public static void updateFCzbk(int dwid){
        //update uf_fzysrxm set yhpje=0,hpsyje=0,yjsrsy=yjsr,yqndyjjzyyje=0,yqndyjjzsyje=yqndyjjzje,snjzhpje=0,snjzhpsyje=0
        //update uf_fzysr set yhpje=0,hpsyje=0,yjsrsy=yjsr,yqndyjjzyyje=0,yqndyjjzsyje=yqndyjjzje,snjzhpje=0,snjzhpsyje=0

        //先查询其他收入与跟地方财政拨款项目
        String sql1 = "select yssrfl,id from uf_fzysrxm where nf=2023 and yssrfl in (5,8) and szdw="+dwid;
        RecordSet rs1 = new RecordSet();
        RecordSet dnhp = new RecordSet();
        RecordSet snhp = new RecordSet();
        RecordSetTrans rs = new RecordSetTrans();
        rs.setAutoCommit(false);
        boolean r = true;
        rs1.execute(sql1);
        while (rs1.next()){
            String id = rs1.getString("id");//收入项目id
            String yssrfl = rs1.getString("yssrfl");//分类id

            //String getDNHpje = "select isnull(sum(hpje),0)as hpje from uf_ysbzyxeshpsj_dt2 where zcqd="+yssrfl+" and dfczbkxmdnys="+id;
            String getDNHpje = "select isnull(sum(je),0)as hpje from uf_jingfeilaiyuan where yslylx='当年预算核批' and  zcqd="+yssrfl+" and dfczbkxm="+id;
            dnhp.execute(getDNHpje);
            dnhp.next();
            Double dnhpje = dnhp.getDouble("hpje");//渠道的核批金额

            //String getSNHpje = "select isnull(sum(hpje),0)as hpje from uf_ysbzyxeshpsj_dt1 where zcqd="+yssrfl+" and dfczbkxmsnjz="+id;
            String getSNHpje = "select isnull(sum(je),0)as hpje from uf_jingfeilaiyuan where yslylx='上年结转核批' and zcqd="+yssrfl+" and dfczbkxm="+id;
            snhp.execute(getSNHpje);
            snhp.next();
            Double snhpje = snhp.getDouble("hpje");//渠道的核批金额
            try {
                updateHPJE_FZYXM(rs,id,dnhpje,snhpje,yssrfl,dwid);
            } catch (Exception e) {
                r = false;
                e.printStackTrace();
            }
        }

        //非其他收入和地方拨款项目--当年核批
//        String sql2 = "select sum(hpje) hpje,zcqd from uf_ysbzyxeshpsj_dt2 where mainid in (select lcjlsqjlid from uf_jingfei_version where ysnd=2023 and  dlhsdw="+dwid+")\n" +
//                "and zcqd not in (5,8) and zcqd <>'' group by zcqd";
        String sql2 = "select sum(je) hpje,zcqd from uf_jingfeilaiyuan where yslylx='当年预算核批' and  lcid in (select lcjlsqjlid from uf_jingfei_version where ysnd=2023 and  dlhsdw="+dwid+")\n" +
                "and zcqd not in (5,8) and zcqd <>'' group by zcqd";
        RecordSet rs3 = new RecordSet();
        rs3.execute(sql2);
        while (rs3.next()){
            Double dnhpje = rs3.getDouble("hpje");
            String zcqd = rs3.getString("zcqd");
            try {
                updateHPJE_FZY(rs,dnhpje,0.0,zcqd,dwid);
            }catch (Exception e){
                r = false;
                e.printStackTrace();
            }
        }
        //非其他收入和地方拨款项目--上年核批
//        String sql3 = "select sum(hpje) hpje,zcqd from uf_ysbzyxeshpsj_dt1 where mainid in (select lcjlsqjlid from uf_jingfei_version where ysnd=2023 and  dlhsdw="+dwid+")\n" +
//                "and zcqd not in (5,8) and zcqd <>'' group by zcqd";
        String sql3 = "select sum(je) hpje,zcqd from uf_jingfeilaiyuan where yslylx='上年结转核批' and  lcid in (select lcjlsqjlid from uf_jingfei_version where ysnd=2023 and  dlhsdw="+dwid+")\n" +
                "and  zcqd not in (5,8) and zcqd <>'' group by zcqd";
        RecordSet rs4 = new RecordSet();
        rs4.execute(sql3);
        while (rs4.next()){
            Double snhpje = rs4.getDouble("hpje");
            String zcqd = rs4.getString("zcqd");
            try {
                updateHPJE_FZY(rs,0.0,snhpje,zcqd,dwid);
            }catch (Exception e){
                r = false;
                e.printStackTrace();
            }
        }
        if(!r){
            rs.rollback();
        }else {
            rs.commit();
        }
    }

    /**
     * 更新其他收入与跟地方财政拨款项目
     * @param rs
     * @param id
     * @param dnhpje
     * @param snhpje
     * @param yssrfl
     * @param dwid
     */
    public static void updateHPJE_FZYXM(RecordSetTrans rs, String id,Double dnhpje,Double snhpje,String yssrfl,int dwid) throws Exception {
        boolean result1 = rs.executeUpdate("update uf_fzysrxm set yhpje=yhpje+"+dnhpje+",hpsyje=hpsyje+"+dnhpje+",yjsrsy=yjsr-yhpje-"+dnhpje+
                ",snjzhpje=snjzhpje+"+snhpje+",snjzhpsyje=snjzhpsyje+"+snhpje+",yqndyjjzsyje=yqndyjjzje-snjzhpje-"+snhpje+" where id="+id);
        System.err.println("更新非中央收入项目 id="+id+" 更新结果为"+result1);
        boolean result2 = rs.executeUpdate("update uf_fzysr set yhpje=yhpje+"+dnhpje+",hpsyje=hpsyje+"+dnhpje+",yjsrsy=yjsr-yhpje-"+dnhpje+
                ",snjzhpje=snjzhpje+"+snhpje+",snjzhpsyje=snjzhpsyje+"+snhpje+",yqndyjjzsyje=yqndyjjzje-snjzhpje-"+snhpje+" where nf=2023 and dw="+dwid+" and szfl="+yssrfl);
        System.err.println("更新非中央级 srfl="+yssrfl+"  单位id="+dwid+" 更新结果为"+result2);
    }

    /**
     * 更新除开其他收入与跟地方财政拨款项目的非财政拨款核批
     * @param rs
     * @param dnhpje
     * @param snhpje
     * @param yssrfl
     * @param dwid
     * @throws Exception
     */
    public static void updateHPJE_FZY(RecordSetTrans rs,Double dnhpje,Double snhpje,String yssrfl,int dwid) throws Exception {
        boolean result2 = rs.executeUpdate("update uf_fzysr set yhpje=yhpje+"+dnhpje+",hpsyje=hpsyje+"+dnhpje+",yjsrsy=yjsr-yhpje-"+dnhpje+
                ",snjzhpje=snjzhpje+"+snhpje+",snjzhpsyje=snjzhpsyje+"+snhpje+",yqndyjjzsyje=yqndyjjzje-snjzhpje-"+snhpje+" where nf=2023 and dw="+dwid+" and szfl="+yssrfl);
        System.err.println("更新非中央级 srfl="+yssrfl+"  单位id="+dwid+" 更新结果为"+result2);
    }
}
